package top.codedance.iotp.client.plugins.sync.db.mssql;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import top.codedance.iotp.common.entity.PluginResultEntity;
import org.ini4j.Wini;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * 连接数据库工具类
 */
public class DBUtil {
    private static final Map<Integer, ConnectionPool> connectionPools = new HashMap<>();

    private Connection connection = null;
    private Statement statement = null;
    private Wini ini = null;

    public DBUtil(Wini ini) {
        this.ini = ini;
    }

    private Connection getConnection(Integer clientId) {
        if(!connectionPools.containsKey(clientId)){
            connectionPools.put(clientId, new ConnectionPool(20, 2, this.ini));
        }
        try {
            connection = connectionPools.get(clientId).getConnection(clientId);
        } catch (InterruptedException throwables) {
            try {
                if (statement != null) {
                    statement.close();
                    statement = null;
                }
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (SQLException e) {
            }
        }
        return connection;
    }

    private Statement getStatement(Integer clientId) {
        if (statement == null) {
            try {
                Connection connection = getConnection(clientId);
                if (connection == null) return null;
                statement = getConnection(clientId).createStatement();
            } catch (SQLException throwables) {
                try {
                    if (statement != null) {
                        statement.close();
                        statement = null;
                    }
                    if (connection != null) {
                        connection.close();
                        connection = null;
                    }
                } catch (SQLException e) {
                }
            }
        }
        return statement;
    }

    public PluginResultEntity executeQuery(Integer clientId, String sql) {
        return executeQueryByConnectionNumber(clientId, sql, 0);
    }

    private PluginResultEntity executeQueryByConnectionNumber(Integer clientId, String sql, int number) {
        number++;
        PluginResultEntity result = new PluginResultEntity();
        statement = getStatement(clientId);
        if (statement == null) {
            result.setOk(false);
            result.setMsg("连接失败");
            return result;
        }
        ResultSet resultSet = null;
        try {
            resultSet = statement.executeQuery(sql);
            ResultSetMetaData md = resultSet.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            JSONArray jsonArray = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    //获取指定列的名称
                    //jsonObject.put(md.getColumnName(i), resultSet.getObject(i));
                    //获取指定列的别名
                    jsonObject.put(md.getColumnLabel(i), resultSet.getObject(i));
                }
                jsonArray.add(jsonObject);
            }
            result.setContext(jsonArray);
            result.setOk(true);
        } catch (SQLException e) {
            if (number >= 2) {
                result.setOk(false);
                result.setMsg(e.getMessage());
            } else {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (statement != null) {
                        statement.close();
                        statement = null;
                    }
                } catch (SQLException e1) {
                    result.setOk(false);
                    result.setMsg(e1.getMessage());
                }
                executeQueryByConnectionNumber(clientId, sql, number);
            }
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }
            } catch (SQLException e1) {
            }
            try {
                if (!connection.isClosed()) {
                    connectionPools.get(clientId).returnConnection(connection);
                    connection = null;
                }
            } catch (SQLException e1) {
            }
        }
        return result;
    }

}
